package com.imesne.office.excel.read;

import com.imesne.office.excel.exception.OfficeException;
import com.imesne.office.excel.model.ExcelCell;
import com.imesne.office.excel.model.ExcelRow;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.InputStream;
import java.util.Iterator;

/**
 * 大数据量excel文件读取
 * <p>
 * Created by liyd on 17/7/6.
 */
public class ExcelSaxReader {


    /**
     * 读取excel文件
     *
     * @param path            文件路径
     * @param excelSaxHandler
     */
    public void read(String path, ExcelSaxReadHandler excelSaxHandler) {
        this.read(new File(path), excelSaxHandler);
    }

    /**
     * 读取excel
     *
     * @param file            excel文件
     * @param excelSaxHandler
     */
    public void read(File file, ExcelSaxReadHandler excelSaxHandler) {
        try {
            OPCPackage pkg = OPCPackage.open(file);
            this.read(pkg, excelSaxHandler);
        } catch (Exception e) {
            throw new OfficeException("读取excel文件失败", e);
        }
    }

    /**
     * 读取excel
     * 注意：此方法因为是整个流的操作，相对于前两个，容易导致内存溢出
     *
     * @param inputStream     excel文件流
     * @param excelSaxHandler
     */
    public void read(InputStream inputStream, ExcelSaxReadHandler excelSaxHandler) {
        try {
            OPCPackage pkg = OPCPackage.open(inputStream);
            this.read(pkg, excelSaxHandler);
        } catch (Exception e) {
            throw new OfficeException("读取excel文件失败", e);
        }
    }


    private void read(OPCPackage pkg, ExcelSaxReadHandler excelSaxHandler) throws Exception {
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(pkg);
        XSSFReader reader = new XSSFReader(pkg);
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        StylesTable stylesTable = reader.getStylesTable();
        DataFormatter formatter = new DataFormatter();
        ContentHandler handler = new XSSFSheetXMLHandler(stylesTable, readOnlySharedStringsTable, new SaxSheetContentsHandler(excelSaxHandler), formatter, false);
        parser.setContentHandler(handler);

        Iterator<InputStream> sheetsData = reader.getSheetsData();
        while (sheetsData.hasNext()) {
            InputStream is = sheetsData.next();
            try {
                InputSource sheetSource = new InputSource(is);
                parser.parse(sheetSource);
            } finally {
                is.close();
            }
        }
    }

    private static class SaxSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

        private ExcelSaxReadHandler excelSaxReadHandler;

        private boolean firstCellOfRow = true;

        private ExcelRow excelRow;

        protected SaxSheetContentsHandler(ExcelSaxReadHandler excelSaxReadHandler) {
            this.excelSaxReadHandler = excelSaxReadHandler;
        }

        public void startRow(int rowNum) {
            firstCellOfRow = true;
            excelRow = new ExcelRow();
            //和excel界面保持一致
            excelRow.setRowNum(rowNum + 1);
        }

        public void endRow(int rowNum) {
            excelSaxReadHandler.row(excelRow);
            excelRow = null;
        }

        public void cell(String cellRef, String formattedValue, XSSFComment xssfComment) {
            if (firstCellOfRow) {
                firstCellOfRow = false;
            }
            String cellNum = null;
            for (int i = 0; i < cellRef.length(); i++) {
                char c = cellRef.charAt(i);
                if (Character.isDigit(c)) {
                    cellNum = StringUtils.substring(cellRef, 0, i);
                    break;
                }
            }

            ExcelCell cell = excelRow.createCell(cellNum, formattedValue);
            excelSaxReadHandler.cell(cell);
        }

        public void headerFooter(String text, boolean isHeader, String tagName) {

        }
    }
}
